﻿using System;
using System.Collections.Generic;
using System.Text;

namespace SqliteDriver
{
	class SqliteTemplates
	{
		static SqliteTemplates g_source = new SqliteTemplates();
		public static SqliteTemplates Templates { get { return g_source; } }

		// move here from main driver for more flexibility.
		public enum Version { UNKNOWN = 0, Three};

		SqliteTemplates()
		{
			this.Initialise();
		}

		void Initialise()
		{
			// [Specialise: wire sql to commands]
			this.templates = new Dictionary<int, Dictionary<DbView.Commands.Cmnd, string>>();
			this.templates.Add(3, new Dictionary<DbView.Commands.Cmnd,string>());
			// populate
			Dictionary<DbView.Commands.Cmnd, String> t = this.templates[3];
			t.Add(DbView.Commands.Cmnd.Aggregate, AGGREGATE_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Fields, FIELDS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.ForeignKeys, FK_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Indexes, INDEX_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Tables, TABLES_SQLTEMPLATE);
			//t.Add(DbView.Commands.Cmnd.Triggers, TRIGGERS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Views, VIEWS_SQLTEMPLATE);
			//t.Add(DbView.Commands.Cmnd.ViewSourceSearch, VIEWSOURCESEARCH_SQLTEMPLATE);
			//t.Add(DbView.Commands.Cmnd.TriggerSourceSearch, TRIGGERSOURCESEARCH_SQLTEMPLATE);
			//t.Add(DbView.Commands.Cmnd.TableFieldSearch, SEARCHFIELDS_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.RowCount, COUNT_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.MinMax, MINMAX_SQLTEMPLATE);
			t.Add(DbView.Commands.Cmnd.Average, AVG_SQLTEMPLATE);

		}
		
		public String FetchTemplate(int sqlServerVersion, DbView.Commands.Cmnd command)
		{
			// have had mapping problesms here before
			if (!this.templates.ContainsKey(sqlServerVersion))
				throw new ApplicationException(String.Format("Cannot locate template resource for Sql Server version {0}", sqlServerVersion));
			if (this.templates[sqlServerVersion].ContainsKey(command))
				return this.templates[sqlServerVersion][command];
			return ""; 
		}

		// Sorry about the API parameter confusion. Todo: tidy
		public static String FetchTemplate(Version sqlServerVersion, DbView.Commands.Cmnd command)
		{
			int versionNumber = MapVersion(sqlServerVersion);
			return new SqliteTemplates().FetchTemplate(versionNumber, command);
		}

		public static int MapVersion(Version sqlVersion)
		{
			return (sqlVersion == Version.Three) ? 3 : -1;
		}

		public String FetchExtensionTemplate(int sqlServerVersion, DbView.Command command, String extensionCommandTag)
		{
			// [Specialise: wire extension command sql to commands]
			switch (extensionCommandTag.ToUpper())
			{
				case Cmd.PragmaIntegrity: return "PRAGMA integrity_check";
				case Cmd.Encoding: return "PRAGMA encoding";
				// could test whether replication is enabled for this database
				case Cmd.DisplayHeldTable: return "-- Nothing";
			}
			return "";
		}

		// process [[ ]] filter clause
		// 
		public String ProcessFilterClause(String clause, String filterText)
		{
            return this.ProcessFilterClause(clause, "[", filterText);
		}

		public String ProcessFilterClause(String clause, String delimeter, String filterText)
		{
			String seekSt = ClauseStart(delimeter);
			String seekEnd = ClauseEnd(delimeter);
			int pos1 = clause.IndexOf(seekSt);
			int pos2 = clause.IndexOf(seekEnd);
			// if [[ ]] not present nothing to do
			if (pos1 == -1 || pos2 == -1 || pos2 < pos1) return clause;
			// 
			pos2 += 2; // advance to end
			String filterClause = clause.Substring(pos1, (pos2 - pos1));
			if (filterText.Length == 0)
			{
				return this.ProcessFilterClause(clause.Replace(filterClause, ""), filterText);
			}
			// make comparison text u/c if database is case sensitive.
			String usedFilterText = filterText;
			// escape '
			usedFilterText = usedFilterText.Replace("'", "''");
			// Check filter text for % and remove default
			if (filterText.IndexOf('%') != -1)
				filterClause = filterClause.Replace("'%%'", "'"+usedFilterText+"'");
			else
				filterClause = filterClause.Replace("'%%'", "'%"+usedFilterText+"%'");
			// check clause for _ and remove REPLACE()
			if (filterText.IndexOf("_") != -1)
			{
				filterClause = filterClause.Replace("REPLACE(","")    //
				               .Replace(",'_','')", "");  //
			}
			// if case insensitive remove UPPER()
			filterClause = filterClause.Replace("UPPER(","")    //
				            .Replace(") like", " like");  //
			
			String x = clause.Substring(0, pos1) + 
			           filterClause.Replace(seekSt,"").Replace(seekEnd, "") + 
			           clause.Substring(pos2);
			
			// process more than one clause
			return this.ProcessFilterClause(x, delimeter, filterText);
		}

		private static string ClauseStart(string delimeter)
		{
			return delimeter + delimeter; // [ -> [[
		}

		private static string ClauseEnd(string delimeter)
		{
			String delend = (delimeter == "{") ? "}" :
			                (delimeter == "[") ? "]" : "]"; // add extras if needed.
			return delend + delend; // { -> }}
		}

		Dictionary<int, Dictionary<DbView.Commands.Cmnd, String>> templates;

		public const String TABLENAMES_SQLTEMPLATE = @"
SELECT name FROM sqlite_master WHERE type='table'
";

		public const String TABLE_SCHEMA_SQLTEMPLATE = @"
SELECT sql FROM sqlite_master WHERE type='table' and name='{0}'
";
		public const String FK_SQLTEMPLATE = @"
SELECT name as Name FROM sqlite_master WHERE type='table' [[and name like '%%']]
";

		const String TABLES_SQLTEMPLATE = @"
SELECT name as Name, NULL as Rows, sql as Schema
    FROM sqlite_master 
   WHERE type='table'
      [[and name like '%%']]
ORDER by name
";
		const String VIEWS_SQLTEMPLATE = @"
SELECT name, rootpage, sql 
    FROM sqlite_master 
   WHERE type='view'
      [[and name like '%%']]
ORDER by name
";
		const String FIELDS_SQLTEMPLATE_OLD = @"
SELECT sql, name
    FROM sqlite_master 
   WHERE (type='table' and name = '{0}')
      OR (type='index' and sql like '%{0}%')
 ORDER BY type desc
     -- Unwanted [[where UPPER(f.COLUMN_NAME) like '%%']]
;
-- fields parsed
PRAGMA table_info({0});
-- fk
PRAGMA foreign_key_list({0});
-- index
PRAGMA index_list({0});

";

//        const String FIELDS_SQLTEMPLATE = @"
//-- fields parsed
//PRAGMA table_info({0});
//-- fk
//PRAGMA foreign_key_list({0});
//-- index
//PRAGMA index_list({0});
//-- schema
//SELECT sql, name
//   FROM sqlite_master 
//   WHERE (type='table' and name = '{0}')
//     -- Unwanted [[where UPPER(f.COLUMN_NAME) like '%%']]
//;
//";
     const String FIELDS_SQLTEMPLATE = @"
PRAGMA table_info({0});
     -- Unwanted [[where UPPER(f.COLUMN_NAME) like '%%']]
";

		const String INDEX_SQLTEMPLATE = @"
SELECT name, sql 
    FROM sqlite_master 
   WHERE type='index'
      [[and name like '%%']]
ORDER by name
";
		const String AGGREGATE_SQLTEMPLATE = @"
select 
    IFNULL([{0}],'{4}') as [{0}], 
    count([{0}]) as [COUNT] 
from ( select IFNULL([{0}],'{4}') as [{0}] 
       from {1}
      where IFNULL([{0}],'{4}') like '%{2}%' -- {5}
     ) sq 
group by [{0}] 
order by count([{0}]) desc, [{0}]
limit {3}
";
		const String AGGREGATE_SQLTEMPLATE_ALT = @"
select top {3} 
    [{0}], 
    count([{0}]) as [COUNT] 
from ( select [{0}] 
       from {1}
     ) sq 
group by [{0}] 
order by count([{0}]) desc, [{0}] -- Can't search {2}
";

		const String COUNT_SQLTEMPLATE = @"
select '{2}' as [{0}], 
        count([{0}]) as [COUNT] from {1} 
    where UPPER([{0}]) like '%{2}%' {3}
";
const String MINMAX_SQLTEMPLATE = @"
select '{2}' as [{0}], 
        min([{0}]) as [MIN], 
        max([{0}]) as [MAX] from {1} 
    where UPPER([{0}]) like '%{2}%' {3}
";

const String AVG_SQLTEMPLATE = @"
select '{2}' as [{0}], 
        sum(cast ([{0}] as float)) as [SUM], 
        avg(cast([{0}] as float)) as [AVERAGE] from {1} 
    where UPPER([{0}]) like '%{2}%' {3}
";

	}
}
